﻿
CREATE VIEW [dbo].[fares_DocumenteDimConturi]
AS
SELECT     t1.NrDoc, t1.DataDoc, t1.CodUnitate, t1.valoare1, t1.valoare2, t1.valoare3, t1.valoare4, t1.DenArticol, t1.ContDebit, t1.ContCredit, t1.Valoare, t1.Denumire, t1.csd_cont, 
                      t1.csc_cont, t1.IDTipDocument, t1.IDPartener, t1.IDArticol, dbo.GetSintetic(t1.ContDebit) AS SinteticContDebit, dbo.GetSintetic(t1.ContCredit) AS SinteticContCredit, 
                      dbo.GetOrderString(t1.ContDebit) AS OrderContDebit, dbo.GetOrderString(t1.ContCredit) AS OrderContCredit, c1.Denumire AS DenDebit, c2.Denumire AS DenCredit
FROM         (SELECT     a.NrDoc, a.DataDoc, a.CodUnitate, c.valoare1, c.valoare2, c.valoare3, c.valoare4, ar.Denumire AS DenArticol, 
                                              CASE WHEN nc.ContDebit = '{ART.CNT}' THEN ar.Cont WHEN nc.ContDebit = '{TVA.DED}' THEN csd.Cont WHEN nc.ContDebit = '{TVA.COL}' THEN csc.Cont WHEN
                                               nc.ContDebit = '{PRT.CCL}' THEN p.ContClient WHEN nc.ContDebit = '{PRT.CNT}' THEN p.Cont WHEN nc.ContDebit = '{ART.CLT}' THEN ar.ContCheltuiala WHEN
                                               nc.ContDebit = '{ART.VEN}' THEN ar.ContVenit ELSE 'N/A' END AS ContDebit, 
                                              CASE WHEN nc.ContCredit = '{PRT.CNT}' THEN p.Cont WHEN nc.ContCredit = '{PRT.CCL}' THEN p.ContClient WHEN nc.ContCredit = '{TVA.DED}' THEN csd.Cont
                                               WHEN nc.ContCredit = '{TVA.COL}' THEN csc.Cont WHEN nc.ContCredit = '{ART.CLT}' THEN ar.ContCheltuiala WHEN nc.ContCredit = '{ART.VEN}' THEN ar.ContVenit
                                               WHEN nc.ContCredit = '{ART.CNT}' THEN ar.Cont ELSE 'N/A' END AS ContCredit, 
                                              CASE WHEN nc.Valoare = '{DOC.VAL}' THEN dd.ValoareTotala WHEN nc.Valoare = '{DOC.TVA}' THEN dd.ValoareTVA WHEN nc.Valoare = '{DOC.VFTVA}' THEN
                                               dd.ValoareTotala - dd.ValoareTVA WHEN nc.Valoare = '{DOC.PVA}' THEN dd.ValoareTotalaCuAdaos WHEN nc.Valoare = '{DOC.PVAVAL}' THEN (dd.ValoareTotalaCuAdaos
                                               * 100) / (100 + dd.CotaTVA) WHEN nc.Valoare = '{DOC.PVAADS}' THEN (dd.ValoareTotalaCuAdaos * 100) / (100 + dd.CotaTVA) 
                                              - dd.ValoareTotala ELSE 0 END AS Valoare, td.Denumire, csd.Cont AS csd_cont, csc.Cont AS csc_cont, td.IDTipDocument, p.IDPartener, ar.IDArticol
                       FROM          dbo.Documente AS a INNER JOIN
                                              dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                                              dbo.TipDocument AS td ON a.IDTipDocument = td.IDTipDocument LEFT OUTER JOIN
                                              dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                                              dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                                              dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol INNER JOIN
                                              dbo.GenNoteContabile AS nc ON nc.IDTipDocument = td.IDTipDocument LEFT OUTER JOIN
                                              dbo.ConturiSpeciale AS csd ON csd.CodUnitate = a.CodUnitate AND csd.Tipuri LIKE '%' + nc.ContDebit + '%' LEFT OUTER JOIN
                                              dbo.ConturiSpeciale AS csc ON csc.CodUnitate = a.CodUnitate AND csc.Tipuri LIKE '%' + nc.ContCredit + '%') AS t1 LEFT OUTER JOIN
                      dbo.Conturi AS c1 ON c1.Cont = t1.ContDebit AND c1.CodUnitate = t1.CodUnitate LEFT OUTER JOIN
                      dbo.Conturi AS c2 ON c2.Cont = t1.ContCredit AND c2.CodUnitate = t1.CodUnitate